package fr.esiea.es_inf5042.persistance.dao.SQLiteDAO;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.location.Location;

import fr.esiea.es_inf5042.business.Bargain;
import fr.esiea.es_inf5042.business.Category;
import fr.esiea.es_inf5042.business.Comment;
import fr.esiea.es_inf5042.business.User;
import fr.esiea.es_inf5042.persistance.dao.AbstractDAOFactory;
import fr.esiea.es_inf5042.persistance.dao.DAO;

public class SQLiteBargainDAO extends DAO<Bargain>{
	
	private SQLiteDatabase database;
		
	private final String COL_FK_USER = "fk_user";
	private final String COL_TITLE = "title";
	private final String COL_DESCRIPTION = "description";
	private final String COL_CATEGORY = "category";
	private final String COL_AVERAGE_MARK = "average_mark";
	private final String COL_ALTITUDE = "altitutde";
	private final String COL_LATITUDE = "latitude";
	private final String COL_LONGITUDE = "longitude";
	
	
	public SQLiteBargainDAO(SQLiteDatabase database) {
			
		this.database = database;
	}

	@Override
	public List<Bargain> getAll(String whereClauses) {

		AbstractDAOFactory daoFactory = null;
		DAO<User> userDAO = null;			
		List<Bargain> resultList = null;
		Bargain b = null;
		Location loc = null;
		Cursor cursor = null;
		String sql = null;
		
		daoFactory = AbstractDAOFactory.getDAOFactory(SQLiteDAOFactory.getContext());
		userDAO = daoFactory.getUserDAO();		
		resultList = new ArrayList<Bargain>();
		
		//KSE - Get all bargains from database.
		sql = "SELECT * FROM " + TABLE_BARGAINS;
		if(whereClauses != null) sql = sql.concat(" WHERE " + whereClauses);
		
		cursor =  database.rawQuery(sql, null);
		
		if(cursor.moveToFirst())
		{
			do
			{			
				loc = new Location(cursor.getString(2));
				loc.setAltitude(cursor.getDouble(6));
				loc.setLatitude(cursor.getDouble(7));
				loc.setLongitude(cursor.getDouble(8));
				
				b = new Bargain(
									cursor.getInt(0),
									userDAO.find(cursor.getInt(1)),
									cursor.getString(2),
									cursor.getString(3),
									null,
									Category.values()[cursor.getInt(4)], 
									cursor.getFloat(5), 
									loc
								);				
						
				resultList.add(b);
			}
			while(cursor.moveToNext());
		}
		
		database.close();
		return resultList;
	}

	@Override
	public Bargain find(int id) {
	
		AbstractDAOFactory daoFactory = null;
		DAO<User> userDAO = null;
		DAO<Comment> commentDAO = null;					
		Bargain b = null;
		Location loc = null;
		Cursor cursor = null;
		String sql = null;
		
		daoFactory = AbstractDAOFactory.getDAOFactory(SQLiteDAOFactory.getContext());
		userDAO = daoFactory.getUserDAO();
		commentDAO = daoFactory.getCommentDAO();				
		
		//KSE - Get user by id attribute.
		sql = "SELECT * FROM " + TABLE_BARGAINS + " WHERE id = "+ String.valueOf(id);		
		
		cursor =  database.rawQuery(sql, null);
		
		if(cursor.moveToFirst())
		{
						
			loc = new Location(cursor.getString(2));
			loc.setAltitude(cursor.getDouble(6));
			loc.setLatitude(cursor.getDouble(7));
			loc.setLongitude(cursor.getDouble(8));
				
			b = new Bargain(
								cursor.getInt(0),
								userDAO.find(cursor.getInt(1)),
								cursor.getString(2),
								cursor.getString(3),
								commentDAO.getAll("fk_bargain = " + Integer.valueOf(id)),
								Category.values()[cursor.getInt(4)], 
								cursor.getFloat(5), 
								loc
							);															
		}
		
		database.close();
		return b;
	}

	@Override
	public Bargain create(Bargain obj) {
					
		String sql = null;
		Cursor cursor = null;
		AbstractDAOFactory daoFactory = null;
		DAO<Comment> commentDAO = null;
		Iterator<Comment> it = null;	
		
		daoFactory = AbstractDAOFactory.getDAOFactory(SQLiteDAOFactory.getContext());
		commentDAO = daoFactory.getCommentDAO();				
		
		ContentValues args = new ContentValues();			
		
		//KSE - Create new entry into database.				
		args.put(COL_FK_USER, obj.getUser().getId());
		args.put(COL_TITLE, obj.getTitle());
		args.put(COL_DESCRIPTION, obj.getDescription());
		args.put(COL_CATEGORY, obj.getCategory().ordinal());
		args.put(COL_AVERAGE_MARK, obj.getAverageMark());
		args.put(COL_ALTITUDE, obj.getLocation().getAltitude());		
		args.put(COL_LATITUDE, obj.getLocation().getLatitude());
		args.put(COL_LONGITUDE, obj.getLocation().getLongitude());
		
		database.insert(TABLE_BARGAINS, null, args);
		
		//KSE - create comments	
		if(obj.getComments() != null)
		{
			it = obj.getComments().iterator();
			while(it.hasNext())
			{
				commentDAO.create(it.next());
			}
		}
		
		//KSE - Get the current entry from the database.
		sql  = "SELECT * FROM " + TABLE_BARGAINS + " WHERE id = last_insert_rowid()";		
		cursor = database.rawQuery(sql, null);		
		if(cursor.moveToFirst())
			obj.setId(cursor.getInt(0));
				
		database.close();
		return obj;
	}

	@Override
	public void update(Bargain obj) {
		
		AbstractDAOFactory daoFactory = null;
		DAO<Comment> commentDAO = null;
		List<Comment> savedComments = null;
		Iterator<Comment> it = null;		
		String sql = null;
		
		daoFactory = AbstractDAOFactory.getDAOFactory(SQLiteDAOFactory.getContext());
		commentDAO = daoFactory.getCommentDAO();
		
		//KSE - Update the bargain attribute "obj".
		sql = "UPDATE " + TABLE_BARGAINS + " SET " + 													
													"fk_user = " + String.valueOf(obj.getUser().getId()) + ", " +
													"title = " + "\'" + obj.getTitle() + "\'" + ", " +
													"description = " + "\'" + obj.getDescription() + "\'" + ", " +
													"category = " + String.valueOf(obj.getCategory().ordinal()) + ", " +
													"average_mark = " + String.valueOf(obj.getAverageMark()) + ", " +
													"altitutde = " + String.valueOf(obj.getLocation().getAltitude()) + ", " +
													"latitude = " + String.valueOf(obj.getLocation().getLatitude()) + ", " +
													"longitude = " + String.valueOf(obj.getLocation().getLongitude()) +
											" WHERE " + "id = " + String.valueOf(obj.getId());
		
		database.rawQuery(sql, null);
		
		//KSE - delete comments
		savedComments = commentDAO.getAll("fk_bargain = " + obj.getId());
		
		it = savedComments.iterator();
		while(it.hasNext())
		{
			commentDAO.delete(it.next());
		}
		
		//KSE - create comments
		it = obj.getComments().iterator();
		while(it.hasNext())
		{
			commentDAO.create(it.next());
		}
		
		database.close();
	}

	@Override
	public void delete(Bargain obj) {

		AbstractDAOFactory daoFactory = null;
		DAO<Comment> commentDAO = null;
		List<Comment> savedComments = null;
		Iterator<Comment> it = null;		
		String sql = null;
		
		daoFactory = AbstractDAOFactory.getDAOFactory(SQLiteDAOFactory.getContext());
		commentDAO = daoFactory.getCommentDAO();
		
		sql = "DELETE FROM " + TABLE_BARGAINS + " WHERE id = " + String.valueOf(obj.getId());
		
		database.rawQuery(sql, null);
		
		//KSE - delete comments
		savedComments = commentDAO.getAll("fk_bargain = " + obj.getId());
		
		it = savedComments.iterator();
		while(it.hasNext())
		{
			commentDAO.delete(it.next());
		}
		
		database.close();
	}
}
